#
#修正特定商品在汉溪总仓中的收发明细
#
#需要在.bahs_profile设置环境变量
#export PSI_PASSWORD=xxxxxxx
#export RDS_AGENT=yyyy
#
#在需要使用的程序中按如下方式调用
#load 'util_fix_goods.rb'
#fix_goods('20008880')
#
require 'mysql2'

$rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])
$goodsName = {};
$goodsPrice = {};
$goodsCategory = {};
$goodsCode = {};
$codeToGoods = {};
$WSID = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' #汉溪总仓

def prepare_fixer

    $rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])
    sql = "select * from psi.t_goods"
    res = $rds.query(sql)
    res.each do |res|
        id = res['id'];
        name = res['name'];
        purchase_price = res['purchase_price'];
        category_id = res['category_id'];
        code = res['code'];
        $goodsName.store(id,name);
        $goodsPrice.store(id,purchase_price);
        $goodsCode.store(id,code);
        $codeToGoods.store(code,id);
        $goodsCategory.store(id,category_id);
    end

end

#用于将2018/12/11日Magento系统清理暂挂订单时形成的发货记录处理完并形成
def shipSpecialOders20181211
        prepare_fixer

        start_msid=184483
        while start_msid<184522 do
            puts start_msid
            start_msid += 1
        end
end

#用于清除所有在库存收发明细表中数量为零的无效记录
#概念，直接在SQL窗口中运行即可
def deleteZeroInventoryDetail
        prepare_fixer
=begin
        #delete zero goods_count pw_detail
        select count(*) from psi.t_pw_bill_detail where goods_count = 0
        delete from psi.t_pw_bill_detail where goods_count = 0;

        #check which pw_bill has no details
        select count(*) from psi.t_pw_bill where id not in (select distinct pwbill_id from psi.t_pw_bill_detail)
        delete from psi.t_pw_bill where id not in (select distinct pwbill_id from psi.t_pw_bill_detail)

        #check how many pw_bill is zero number
        select count(*) from psi.t_pw_bill where goods_money = 0
=end
end
#
#用于清除所有在库存收发明细表中测试用户的记录
#概念，直接在SQL窗口中运行即可
def deleteTestUserData
        prepare_fixer
=begin
        #test_user_id ='4A92724C-E898-11E8-8B92-6C92BF475963'

        #check how many pw_bill is zero number
        select count(*) from psi.t_pw_bill where biz_user_id='4A92724C-E898-11E8-8B92-6C92BF475963'

        #delete zero goods_count pw_detail
        select count(*) from psi.t_pw_bill_detail where goods_count = 0
        delete from psi.t_pw_bill_detail where goods_count = 0;

        #check which pw_bill has no details
        select count(*) from psi.t_pw_bill where id not in (select distinct pwbill_id from psi.t_pw_bill_detail)
        delete from psi.t_pw_bill where id not in (select distinct pwbill_id from psi.t_pw_bill_detail)

=end
end

#用于将源商品编码的相关数据移动到目标商品编码
def merge_details(scode,tcode)

    prepare_fixer

    source_gid = $codeToGoods[scode]
    target_gid = $codeToGoods[tcode]
    puts "---> #{source_gid}  ---> #{target_gid}"


    #update po_bill_details
    sql = "select * from psi.t_po_bill_detail where goods_id='#{source_gid}'"
    puts sql
    res = $rds.query(sql)
    res.each do |r|
            pwb_id = r['id']
            sqlu = "update psi.t_po_bill_detail set goods_id='#{target_gid}' where id='#{pwb_id}'"
            puts sqlu
            resu = $rds.query(sqlu)
    end


    #update pw_bill_details, be attentioned that warehouse id is in pw_bill
    sql = "select * from psi.t_pw_bill_detail where goods_id='#{source_gid}'"
    puts sql
    res = $rds.query(sql)
    res.each do |r|
            pwb_id = r['id']
            sqlu = "update psi.t_pw_bill_detail set goods_id='#{target_gid}' where id='#{pwb_id}'"
            puts sqlu
            resu = $rds.query(sqlu)
    end

    #update inventory_details
    sql = "select * from psi.t_inventory_detail where goods_id='#{source_gid}' and warehouse_id='#{$WSID}'"
    puts sql
    res = $rds.query(sql)
    res.each do |r|
            whd_id = r['id']
            sqlu = "update psi.t_inventory_detail set goods_id='#{target_gid}' where id=#{whd_id}"
            puts sqlu
            resu = $rds.query(sqlu)
    end

    #update inventory
    #here's an bug, if target already has record.... TO BE FIX
    sql = "select * from psi.t_inventory where goods_id='#{source_gid}' and warehouse_id='#{$WSID}'"
    puts sql
    res = $rds.query(sql)
    res.each do |r|
        inv_id = r['id']
        sqlu = "update psi.t_inventory set balance_count=0.0, balance_money=0.0, balance_price=#{sprintf('%.3f',$goodsPrice[target_gid])}, goods_id='#{target_gid}' where id='#{inv_id}'"
        puts sqlu
        resu = $rds.query(sqlu)
    end

end


def fix_goods(target_code)

    prepare_fixer

    questionGoods = {};
    target_gid=$codeToGoods[target_code]
    if target_gid != nil then
        target_price=$goodsPrice[target_gid]
        questionGoods.store(target_gid, target_price);
    else
        puts "ERR: target_gid not found for #{target_code}"
        return
    end

    puts "questionGoods total: #{questionGoods.count}"

    questionGoods.each { |gid, suggest_price| 

        #only fix some goods
        if ( $goodsCode[gid] != 'skip this code hahaha' ) then

            puts "=begin to fix: 【#{$goodsCode[gid]}】#{$goodsName[gid]} #{gid} #{sprintf("%.2f", suggest_price)}"

            #先重置库存总帐上的商品项目
            sqlu = "update psi.t_inventory set balance_count=0.0, balance_money=0.0, balance_price=#{sprintf('%.3f',suggest_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
            puts sqlu

            # can't be reused
            $rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])
            resu = $rds.query(sqlu)

            sql2 = "select * from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id"
            puts "sql2: --#{sql2}--"
            res2 = $rds.query(sql2)

            res2.each do |r2|

                #get latest balance

                inv_detail_id = r2['id']
                #get latest balance
                sql3 = "select * from psi.t_inventory where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id"
                puts "sql3: --#{sql3}--"
                res3 = $rds.query(sql3)

                    if !res3.first.nil?

                        b_money = res3.first['balance_money']
                        b_count = res3.first['balance_count']
                        b_price = suggest_price #this can be removed

                        if r2['in_count'].nil? then

                            #出库记录
                            #fix current record
                            out_money = r2['out_money']
                            out_count = r2['out_count']
                            out_price = r2['out_price']
                            if out_price <=0 then out_price = suggest_price end
                            if out_money <=0 then out_money = out_count * out_price end

                            #recal current balance
                            b_money -= out_money
                            b_count -= out_count
                            if b_count != 0 then b_price = b_money / b_count else b_price = suggest_price end
                

                            sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.8f', b_count)}, balance_money=#{sprintf('%.8f', b_money)}, balance_price=#{sprintf('%.8f', b_price)}, out_money=#{sprintf('%.8f', out_money)}, out_price=#{sprintf('%.3f', out_price)}, fixed='fixed' where id='#{inv_detail_id}'"
                            puts sqlu
                            resu = $rds.query(sqlu)
                        
                            #update balance
                            sqlu = "update psi.t_inventory set balance_count=#{sprintf('%.8f',b_count)}, balance_money=#{sprintf('%.8f',b_money)}, balance_price=#{sprintf('%.8f',b_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
                            puts sqlu
                            resu = $rds.query(sqlu)

                        elsif r2['out_count'].nil? then

                            #入库记录
                            #fix current record
                            in_money = r2['in_money']
                            in_count = r2['in_count']
                            in_price = r2['in_price']
                            if in_price <=0 then in_price = suggest_price end
                            if in_money <=0 then in_money = in_count * in_price end

                            #recal current balance
                            b_money += in_money
                            b_count += in_count
                            if b_count != 0 then b_price = b_money / b_count else b_price = suggest_price end

                            sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.8f', b_count)}, balance_money=#{sprintf('%.8f', b_money)}, balance_price=#{sprintf('%.8f', b_price)}, in_money=#{sprintf('%.8f', in_money)}, in_price=#{sprintf('%.3f', in_price)}, fixed='fixed' where id='#{inv_detail_id}'"
                            puts sqlu
                            resu = $rds.query(sqlu)
                        
                            #update balance
                            sqlu = "update psi.t_inventory set balance_count=#{sprintf('%.8f',b_count)}, balance_money=#{sprintf('%.8f',b_money)}, balance_price=#{sprintf('%.8f',b_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
                            puts sqlu
                            resu = $rds.query(sqlu)

                        else
                            puts "Something wrong"
                        end
                end #if !res3.first['id'].nil?

            end

            puts "=end"

        end

    }

    $rds.close

end

def rebuild_inventory_by_goods_id(gid)

    prepare_fixer
    suggest_price=$goodsPrice[gid]

    puts "=begin to fix: 【#{$goodsCode[gid]}】#{$goodsName[gid]} #{gid} #{sprintf("%.2f", suggest_price)}"

    #先重置库存总帐上的商品项目
    sqlu = "update psi.t_inventory set balance_count=0.0, balance_money=0.0, balance_price=#{sprintf('%.3f',suggest_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
    puts sqlu

    # can't be reused
    $rds = Mysql2::Client.new(:host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1401', :password => ENV['PSI_PASSWORD'])
    resu = $rds.query(sqlu)

    sql2 = "select * from psi.t_inventory_detail where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id"
    puts "sql2: --#{sql2}--"
    res2 = $rds.query(sql2)

    res2.each do |r2|

        #get latest balance

        inv_detail_id = r2['id']
        #get latest balance
        sql3 = "select * from psi.t_inventory where warehouse_id='7E6C8D0D-E25A-11E8-8B92-6C92BF475963' and goods_id='#{gid}' order by id"
        puts "sql3: --#{sql3}--"
        res3 = $rds.query(sql3)

            if !res3.first.nil?

                b_money = res3.first['balance_money']
                b_count = res3.first['balance_count']
                b_price = suggest_price #this can be removed

                if r2['in_count'].nil? then

                    #出库记录
                    #fix current record
                    out_money = r2['out_money']
                    out_count = r2['out_count']
                    out_price = r2['out_price']
                    if out_price <=0 then out_price = suggest_price end
                    if out_money <=0 then out_money = out_count * out_price end

                    #recal current balance
                    b_money -= out_money
                    b_count -= out_count
                    if b_count != 0 then b_price = b_money / b_count else b_price = suggest_price end
        

                    sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.8f', b_count)}, balance_money=#{sprintf('%.8f', b_money)}, balance_price=#{sprintf('%.8f', b_price)}, out_money=#{sprintf('%.8f', out_money)}, out_price=#{sprintf('%.3f', out_price)}, fixed='fixed' where id='#{inv_detail_id}'"
                    puts sqlu
                    resu = $rds.query(sqlu)
                
                    #update balance
                    sqlu = "update psi.t_inventory set balance_count=#{sprintf('%.8f',b_count)}, balance_money=#{sprintf('%.8f',b_money)}, balance_price=#{sprintf('%.8f',b_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
                    puts sqlu
                    resu = $rds.query(sqlu)

                elsif r2['out_count'].nil? then

                    #入库记录
                    #fix current record
                    in_money = r2['in_money']
                    in_count = r2['in_count']
                    in_price = r2['in_price']
                    if in_price <=0 then in_price = suggest_price end
                    if in_money <=0 then in_money = in_count * in_price end

                    #recal current balance
                    b_money += in_money
                    b_count += in_count
                    if b_count != 0 then b_price = b_money / b_count else b_price = suggest_price end

                    sqlu = "update psi.t_inventory_detail set balance_count=#{sprintf('%.8f', b_count)}, balance_money=#{sprintf('%.8f', b_money)}, balance_price=#{sprintf('%.8f', b_price)}, in_money=#{sprintf('%.8f', in_money)}, in_price=#{sprintf('%.3f', in_price)}, fixed='fixed' where id='#{inv_detail_id}'"
                    puts sqlu
                    resu = $rds.query(sqlu)
                
                    #update balance
                    sqlu = "update psi.t_inventory set balance_count=#{sprintf('%.8f',b_count)}, balance_money=#{sprintf('%.8f',b_money)}, balance_price=#{sprintf('%.8f',b_price)} where goods_id='#{gid}' and warehouse_id = '7E6C8D0D-E25A-11E8-8B92-6C92BF475963' "
                    puts sqlu
                    resu = $rds.query(sqlu)

                else
                    puts "Something wrong"
                end
        end #if !res3.first['id'].nil?

    end

    puts "=end"



    $rds.close

end
